package implicits

import java.io.{File, FileInputStream}
import java.text.SimpleDateFormat
import org.apache.commons.io.FileUtils
import org.apache.commons.lang3.StringUtils
import org.apache.poi.ss.usermodel.{Cell, CellType, DateUtil, Sheet}
import org.apache.poi.xssf.usermodel.{XSSFCell, XSSFFormulaEvaluator, XSSFSheet, XSSFWorkbook}

import scala.collection.JavaConverters._
import implicits.Implicits._

/**
 * Created by Administrator on 2019/11/11
 */

trait MyXlxsTool {

  implicit class MyXlxsFile(file: File) {

    def getCellValue(cellType: CellType, cell: XSSFCell, formulaEvaluator: XSSFFormulaEvaluator): String = {
      cellType match {
        case CellType.STRING =>
          cell.getStringCellValue.myTrim
        case CellType.NUMERIC =>
          if (DateUtil.isCellDateFormatted(cell)) {
            val dateFormat = new SimpleDateFormat("yyyy/MM/dd")
            dateFormat.format(cell.getDateCellValue)
          } else {
            val doubleValue = cell.getNumericCellValue
            if (doubleValue == doubleValue.toInt) {
              doubleValue.toInt.toString
            } else doubleValue.toString
          }
        case CellType.FORMULA =>
          val resultCellType = formulaEvaluator.evaluate(cell).getCellTypeEnum
          getCellValue(resultCellType, cell, formulaEvaluator)
        case _ =>
          ""
      }
    }

    def getTrueCell(cell: XSSFCell, xssfSheet: XSSFSheet) = {
      getMergedRegion(cell).map { range =>
        val firstCell = xssfSheet.getRow(range.getFirstRow).getCell(range.getFirstColumn)
        firstCell
      }.getOrElse {
        cell
      }
    }

    def xlsxLines(sheetIndex: Int = 0) = {
      val is = new FileInputStream(file.getAbsolutePath)
      val xssfWorkbook = new XSSFWorkbook(is)
      val formulaEvaluator = xssfWorkbook.getCreationHelper.createFormulaEvaluator()
      val xssfSheet = xssfWorkbook.getSheetAt(sheetIndex)
      val lines = (0 to xssfSheet.getLastRowNum).toList.map { i =>
        val xssfRow = xssfSheet.getRow(i)
        val firstRow = xssfSheet.getRow(0)
        (0 until firstRow.getLastCellNum).map { j =>
          val cell = xssfRow.getCell(j)
          val value = if (cell == null) {
            ""
          } else {
            val trueCell = getTrueCell(cell, xssfSheet)
            val cellType = trueCell.getCellTypeEnum
            getCellValue(cellType, trueCell, formulaEvaluator)
          }
          value.trim
        }.toList
      }
      xssfWorkbook.close()
      lines.filter(x => x.exists(y => StringUtils.isNotBlank(y)))
    }

    def getMergedRegion(cell: XSSFCell) = {
      val sheet = cell.getSheet
      sheet.getMergedRegions.asScala.find { range =>
        range.isInRange(cell.getRowIndex, cell.getColumnIndex)
      }
    }

    def xlsx2Txt(txtFile: File) = {
      val lines = file.xlsxLines()
      lines.toFile(txtFile)
    }

  }
}


